package com.dpteam.dao.impl;

import com.dpteam.bean.CountPie;
import com.dpteam.dao.OrderItemDao;
import com.dpteam.model.Order;
import com.dpteam.model.OrderItem;
import com.dpteam.model.Product;
import com.dpteam.util.Page;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Projections;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.Month;
import java.util.*;

/**
 * Created by YangZhi on 2016/1/8.
 */
@Repository
public class OrderItemDaoImpl extends BaseDaoImpl<OrderItem> implements OrderItemDao {

    private static DecimalFormat df=new java.text.DecimalFormat("#.##");

    @Override
    public List<OrderItem> findByCategoryIdPage(Integer categoryId, Page page) {
        String  hql = "from OrderItem o where o.category.id = ? order by o.id";
        return find(hql,page,categoryId);
    }

    @Override
    public List<OrderItem> findByCategoryId(Integer categoryId) {
        String  hql = "from OrderItem o where o.category.id = ?  order by o.id";
        return find(hql,categoryId);
    }

    @Override
    public List<OrderItem> findByProviderIdPage(Integer providerId, Page page) {
        String  hql = "from OrderItem o where o.provider.id = ? order by o.id";
        return find(hql,page,providerId);
    }

    @Override
    public List<OrderItem> findByProviderId(Integer providerId) {
        String  hql = "from OrderItem o where o.provider.id = ? order by o.id";
        return find(hql,providerId);
    }

    @Override
    public List<OrderItem> findByDesTop(String whe,int top) {
        Session session = getCurrentSession();
            List list=session.createCriteria(OrderItem.class)
                    .setProjection(Projections.projectionList()
                                    .add(Projections.sum("productNum").as("productNum"))
                                    .add(Projections.sum("profitTotal"))
                                    .add(Projections.sum("sellPriceTotal"))
                                    .add(Projections.groupProperty("product"))
                    ).addOrder(org.hibernate.criterion.Order.desc(whe)).setMaxResults(top).list();
            List<OrderItem> oiList = new ArrayList<>();
            OrderItem oi ;
            for(Object os:list) {
                Object[] object = (Object[]) os;
                if(object!=null){
                    oi = new OrderItem();

                    long tempL = (long)object[0];
                    double tempd = (double)object[1];
                    oi.setProductNum((int) tempL);
                    oi.setProfitTotal((float) tempd );

                    tempd = (double) object[2];
                    oi.setSellPriceTotal((float)tempd);
                    oi.setProduct((Product) object[3]);
                    oiList.add(oi);
                }
        }
        return oiList;
    }

    @Override
    public List<OrderItem> findByproductId(Integer productId) {
        String  hql = "from OrderItem o where o.product.id = ? order by o.id";
        return find(hql,productId);
    }
    @Override
    public List<OrderItem>  batchInsert(List<OrderItem> orderItemList) {
        Session session = getCurrentSession();
        for (int i = 0; i < orderItemList.size(); i++) {
            session.save(orderItemList.get(i));
            if (i % 20 == 0) {
                session.flush();
                session.clear();
            }
        }
        return orderItemList;
    }

    @Override
    public double sumProfit(int year, int categoryId) {
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, 1, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, 12, 31), LocalTime.MAX);
        String hql = "select sum(profitTotal) from OrderItem where order.submitTime >= ? and order.submitTime <= ? and product.category.id = ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        query.setParameter(2, categoryId);
        Double result = (Double)query.uniqueResult();
//        return result == null ? 0 : result.doubleValue();
        return result == null ? 0 : Double.parseDouble(df.format(result));
    }

    @Override
    public double sumProfit(int year, int month, int categoryId) {
        Month month1 = Month.of(month);
        LocalDate localDate = LocalDate.of(year, month, 1);
        int maxDay = month1.maxLength();
        if (!localDate.isLeapYear() && month == 2) {
            maxDay--;
        }
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, month, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, month, maxDay), LocalTime.MAX);
        String hql = "select sum(profitTotal) from OrderItem where order.submitTime >= ? and order.submitTime <= ? and product.category.id = ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        query.setParameter(2, categoryId);
        Double result = (Double)query.uniqueResult();
//        return result == null ? 0 : result.doubleValue();
        return result == null ? 0 : Double.parseDouble(df.format(result));
    }

    @Override
    public double sumSell(int year, int categoryId) {
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, 1, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, 12, 31), LocalTime.MAX);
        String hql = "select sum(sellPriceTotal) from OrderItem where order.submitTime >= ? and order.submitTime <= ? and product.category.id = ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        query.setParameter(2, categoryId);
        Double result = (Double)query.uniqueResult();
//        return result == null ? 0 : result.doubleValue();
        return result == null ? 0 : Double.parseDouble(df.format(result));
    }

    @Override
    public double sumSell(int year, int month, int categoryId) {
        Month month1 = Month.of(month);
        LocalDate localDate = LocalDate.of(year, month, 1);
        int maxDay = month1.maxLength();
        if (!localDate.isLeapYear() && month == 2) {
            maxDay--;
        }
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, month, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, month, maxDay), LocalTime.MAX);
        String hql = "select sum(sellPriceTotal) from OrderItem where order.submitTime >= ? and order.submitTime <= ? and product.category.id = ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        query.setParameter(2, categoryId);
        Double result = (Double)query.uniqueResult();
//        return result == null ? 0 : result.doubleValue();
        return result == null ? 0 : Double.parseDouble(df.format(result));
    }

    @Override
    public List<CountPie> countProfit(int year) {
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, 1, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, 12, 31), LocalTime.MAX);
        String hql = "select sum(o.profitTotal) as profit, o.product.category.typeName from OrderItem o where o.order.submitTime >= ? and o.order.submitTime <= ? group by o.product.category.id";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        List<CountPie> result = new ArrayList<>();
        List list = query.list();
        for (Object obj : list) {
            Object[] row = (Object[]) obj;
            double profit = (double)row[0];
            String typeName = (String)row[1];
            CountPie countPie = new CountPie(typeName, Double.parseDouble(df.format(profit)), null);
            result.add(countPie);
        }
        return result;
    }

    @Override
    public List<CountPie> countProfit(int year, int month) {
        LocalDate now = LocalDate.of(year, month, 1);
        Month monthNow = Month.of(month);
        int maxDay =  monthNow.maxLength();
        if (!now.isLeapYear() && month == 2) {
            maxDay--;
        }
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, month, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, month, maxDay), LocalTime.MAX);
        String hql = "select sum(o.profitTotal) as profit, o.product.category.typeName from OrderItem o where o.order.submitTime >= ? and o.order.submitTime <= ? group by o.product.category.id";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        List<CountPie> result = new ArrayList<>();
        List list = query.list();
        for (Object obj : list) {
            Object[] row = (Object[]) obj;
            double profit = (double)row[0];
            String typeName = (String)row[1];
            CountPie countPie = new CountPie(typeName, Double.parseDouble(df.format(profit)), null);
            result.add(countPie);
        }
        return result;
    }

    @Override
    public List<CountPie> countSell(int year) {
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, 1, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, 12, 31), LocalTime.MAX);
        String hql = "select sum(o.sellPriceTotal) as profit, o.product.category.typeName from OrderItem o where o.order.submitTime >= ? and o.order.submitTime <= ? group by o.product.category.id";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        List<CountPie> result = new ArrayList<>();
        List list = query.list();
        for (Object obj : list) {
            Object[] row = (Object[]) obj;
            double sell = (double)row[0];
            String typeName = (String)row[1];
            CountPie countPie = new CountPie(typeName, null, Double.parseDouble(df.format(sell)));
            result.add(countPie);
        }
        return result;
    }

    @Override
    public List<CountPie> countSell(int year, int month) {
        LocalDate now = LocalDate.of(year, month, 1);
        Month monthNow = Month.of(month);
        int maxDay =  monthNow.maxLength();
        if (!now.isLeapYear() && month == 2) {
            maxDay--;
        }
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, month, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, month, maxDay), LocalTime.MAX);
        String hql = "select sum(o.sellPriceTotal) as profit, o.product.category.typeName from OrderItem o where o.order.submitTime >= ? and o.order.submitTime <= ? group by o.product.category.id";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        List<CountPie> result = new ArrayList<>();
        List list = query.list();
        for (Object obj : list) {
            Object[] row = (Object[]) obj;
            double sell = (double)row[0];
            String typeName = (String)row[1];
            CountPie countPie = new CountPie(typeName, null, Double.parseDouble(df.format(sell)));
            result.add(countPie);
        }
        return result;
    }
}
